package com.springapp.dao.dao.impl;

import com.springapp.dao.dao.ILigneCommandeDAO;
import com.springapp.model.LigneCommande;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;

/**
 * Created by Thomas on 30/08/14.
 */
@Repository
public class LigneCommandeDAO implements ILigneCommandeDAO{
    @Qualifier("dataSource") //Fait référence au datasource dans le mvc-dispatcher.xml
    @Autowired
    private DataSource dataSource;


    public String getRefCommandeByIDAndByUser(String _sLogin) {


        String sql="SELECT cmd.RefCommande\n" +
                "FROM LigneCommande lcmd\n" +
                "  INNER JOIN Commande cmd ON lcmd.IDCommande=cmd.IDCommande\n" +
                "  INNER JOIN Article art ON lcmd.IDArticle= art.IDArticle\n" +
                "  INNER JOIN Utilisateur usr ON cmd.IDUtilisateur=usr.IDUtil\n" +
                "  INNER JOIN Cmd_Statut cmdStat ON cmd.IDCmdStatut=cmdStat.IDCmdStatut\n" +
                "WHERE cmd.IDCmdStatut=1 AND Cmd.IDUtilisateur=(\n" +
                "  SELECT usr.IDUtil\n" +
                "  FROM Utilisateur usr\n" +
                "  WHERE login='"+_sLogin+"')\n" +
                "ORDER BY cmd.dateLivraisonDemandee ASC";


        Connection conn=null;
        String refcmd = "";

        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet result=ps.executeQuery();
            while(result.next()){
                refcmd = result.getString("RefCommande");

            }
            result.close();
            ps.close();


        } catch (SQLException e) {
            e.printStackTrace();
        }

        return refcmd;
    }


    public List<LigneCommande> getCommandeByIDAndByUser(String _sLogin, String _sRef) {


        String sql="SELECT lcmd.IDLigneCommande, cmd.IDCommande, art.IDArticle,cmd.RefCommande, art.RefArticle, art.libelle AS 'Article',artSousCat.Libelle AS 'Sous-categorie', lcmd.quantiteArt,cmd.montantHT,cmd.TVA, cmd.montantTTC, hyp.nomHypermarche\n" +
                "FROM LigneCommande lcmd\n" +
                "  INNER JOIN Commande cmd ON lcmd.IDCommande=cmd.IDCommande\n" +
                "  INNER JOIN Article art ON lcmd.IDArticle= art.IDArticle\n" +
                "  INNER JOIN Hypermarche hyp ON  cmd.IDHypermarche=hyp.IDHypermarche\n" +
                "  INNER JOIN Utilisateur usr ON cmd.IDUtilisateur=usr.IDUtil\n" +
                "  INNER JOIN Cmd_Statut cmdStat ON cmd.IDCmdStatut=cmdStat.IDCmdStatut\n" +
                "  INNER JOIN Art_SousCategorie artSousCat ON art.IDArt_SousCategorie=artSousCat.IDSousCat\n" +
                "WHERE cmd.IDCmdStatut=1 AND Cmd.IDUtilisateur=(\n" +
                "  SELECT usr.IDUtil\n" +
                "  FROM Utilisateur usr\n" +
                "  WHERE usr.login='"+_sLogin+"')\n" +
                "AND cmd.RefCommande='"+_sRef+"'";


        Connection conn=null;
        List<LigneCommande> lstcmd=new ArrayList<LigneCommande>();


        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet result=ps.executeQuery();
            while(result.next()){
                LigneCommande ligneCommande=new LigneCommande(
                        result.getInt("IDLigneCommande"),
                        result.getInt("IDcommande"),
                        result.getInt("IDArticle"),
                        result.getString("RefCommande"),
                        result.getString("RefArticle"),
                        result.getString("Article"),
                        result.getString("Sous-categorie"),
                        result.getInt("quantiteArt"),
                        result.getFloat("montantHT"),
                        result.getFloat("TVA"),
                        result.getFloat("montantTTC"),
                        result.getString("nomHypermarche")

                );

                lstcmd.add(ligneCommande);
            }
            result.close();
            ps.close();


        } catch (SQLException e) {
            e.printStackTrace();
        }

        return lstcmd;
    }


    public Hashtable<Integer, Integer> htIDNbArt(String sRef){
        Hashtable<Integer,Integer> hPalIDArtNbArt = new Hashtable<Integer, Integer>();



            String sql="SELECT lcmd.IDArticle, lcmd.quantiteArt\n" +
                "FROM ligneCommande lcmd\n" +
                "INNER JOIN Commande cmd ON cmd.IDCommande=lcmd.IDCommande\n" +
                "WHERE cmd.RefCommande="+sRef;
        Connection conn=null;

        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet result=ps.executeQuery();

            while(result.next()){
                hPalIDArtNbArt.put(
                        result.getInt("IDArticle"),
                        result.getInt("quantiteArt"));
            }

            result.close();
            ps.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return hPalIDArtNbArt;
    }
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public ArrayList<Integer> getlstIDarticle(String sRefCmd) {

        ArrayList<Integer> lstIDarticles = new ArrayList<Integer>();

        String sql="select IDArticle\n" +
                "from ligneCommande \n" +
                "inner join commande cmd on cmd.IDCommande = lignecommande.IDCommande\n" +
                "where cmd.RefCommande =" +  sRefCmd ;
        Connection conn=null;

        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet result=ps.executeQuery();

            while(result.next()){
                lstIDarticles.add(result.getInt("IDArticle"));
            }

            result.close();
            ps.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lstIDarticles;
    }
}
